import logging

from sqlalchemy.exc import SQLAlchemyError
from wtforms.validators import data_required as required, ValidationError
from app.core import CommonView
from app import db
from app.core import consts


# 自定义校验器，判断用户输入文件名是否正确、文件是否存在等
def sql_validator(form, field):
    sql_text = field.data

    if len(sql_text.strip()) == 0:
        raise ValidationError(u'SQL脚本不能为空！')


class SQLView(CommonView):
    column_display_actions = True
    can_delete = True
    can_edit = False
    can_create = True
    column_searchable_list = None
    column_list = ('id', 'sql_brief', 'proc_state', 'affect_rows', 'created_at', 'proc_result')
    column_labels = {
        'id': 'ID',
        'sql_brief': 'SQL文本',
        'sql_text': 'SQL文本',
        'sql_type': 'SQL类型',
        'proc_state': '处理状态',
        'affect_rows': '影响行数',
        'proc_result': '执行结果',
        'created_at': '创建时间'
    }
    column_sortable_list = ('id', 'created_at')
    column_default_sort = ('id', True)

    column_filters = ('created_at', 'sql_brief')
    column_choices = {
        'sql_type': [
            ('DML', 'DML'),
            ('DDL', 'DDL'),
        ],
        'proc_state': [
            ('0', '待执行'),
            ('1', '执行成功'),
            ('2', '执行失败'),
        ],
    }
    column_formatters = {
        'proc_result': lambda v, c, m, p: m.proc_result[:50] if m.proc_result is not None else ''
    }

    page_size = 20
    form_columns = ('sql_type', 'sql_text', 'proc_state', 'affect_rows', 'proc_result')
    form_choices = column_choices
    column_details_exclude_list = ['sql_brief']

    form_args = {
        'sql_text': {
            'validators': [required(), sql_validator],
            'render_kw': {
                'placeholder': '请输入SQL文本'
            }
        },
        'sql_type': {
            'validators': [required()],
        },
        'proc_state': {
            'validators': [required()],
        },
    }

    form_widget_args = {
        'proc_state': {
            'disabled': True
        },
        'affect_rows': {
            'disabled': True
        },
        'proc_result': {
            'disabled': True,
        },
    }

    # def on_form_prefill(self, form, id):
    #     form.proc_state.render_kw = {'selected': True}

    def after_model_change(self, form, model, is_created):
        """当完成创建后，执行文件解析和导数入库操作！"""
        from app import logger
        if is_created:
            logger.info('data file loading...')
            if hasattr(model, 'sql_text') and hasattr(model, 'sql_type'):
                sql_text = model.sql_text
                model.gen_sql_brief()
                sql_type = model.sql_type
                try:
                    # execute sql-script
                    from sqlalchemy import text
                    import json
                    rs = db.session.execute(text(sql_text))
                    if rs is not None and sql_text[:6].upper() == 'SELECT':
                        if ' FROM '.find(sql_text.upper()) >= 0:
                            rs_list = []
                            for r in rs.all():
                                rs_list.append(r.to_dict())
                            model.proc_result = json.dumps(rs_list)
                        else:
                            rs_list = list[rs]
                            model.proc_result = json.dumps(str(rs_list[0]))
                    affect_rows = rs.rowcount
                    model.affect_rows = affect_rows
                    model.proc_state = '1'
                    db.session.commit()
                except SQLAlchemyError as se:
                    print(se)
                    db.session.rollback()
                    model.gen_sql_brief()
                    model.proc_result = '执行失败：' + str(se)
                    model.proc_state = '2'
                    db.session.commit()
                except Exception as ex:
                    db.session.rollback()
                    model.gen_sql_brief()
                    model.proc_result = 'err!' + str(ex)
                    model.proc_state = '2'
                    db.session.commit()
                finally:
                    db.session.close()
